﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

#if MS_ACCESS
    using System.Data.OleDb;
#else
    using MySql.Data.MySqlClient;
#endif

using System.Security.Principal;
using StonePonyMVC.StonePony.Classes;
using System.Web.Security;
using StonePonyMVC.Models;
using System.Data;

namespace StonePonyMVC.StonePony.Services
{
    /// <summary>
    /// Custom authentication in Pony.mdb database
    /// </summary>
    public class MembershipService : IMembershipService
    {
        private readonly string _connectionString;

        /// <summary>
        /// Read connection string from <code>Web.config</code> 
        /// </summary>
        public MembershipService()
        {
#if MS_ACCESS
            _connectionString = ConfigurationManager.ConnectionStrings["StonePonyServices"].ConnectionString;
#else
            _connectionString = ConfigurationManager.AppSettings["StonePonyServices"];
#endif
        }

        #region IMembershipService Members

        public bool ValidateUser(string email, string password, HttpContextBase context)
        {
            StonePonyIdentity identity = ReadUser(email);
            bool rzlt = identity.UserPassword == password;
            identity.IsAuthenticated = rzlt;

            context.User = new CurrentUser(identity);
            return rzlt;
        }

        public StonePonyIdentity BuildStonePonyIdentity(string email, bool isAuthenticated)
        {
            StonePonyIdentity identity = ReadUser(email);
            identity.IsAuthenticated = isAuthenticated;
            return identity;
        }

        public MembershipCreateStatus CreateUser(RegisterModel registerData)
        {
            MembershipCreateStatus status;
            if ((status = ValidateData(registerData.Email)) == MembershipCreateStatus.Success)
            {
                status = CreateOneUser(registerData);
            }
            return status;
        }

        public bool ChangePassword(string email, string oldPassword, string newPassword)
        {
            const string sqlTemplate = "UPDATE users SET user_password='{0}' WHERE (user_email='{1}' AND user_password='{2}')";
            try
            {
#if MS_ACCESS
                using (var connection = new OleDbConnection(_connectionString))
                {
                    var command = new OleDbCommand(String.Format(sqlTemplate, newPassword, email, oldPassword), connection);
                    connection.Open();
                    int rowCount = command.ExecuteNonQuery();
                    connection.Close();
                    return rowCount > 0;
                }
#else
                using (var connection = new MySqlConnection(_connectionString))
                {
                    var command = new MySqlCommand(String.Format(sqlTemplate, newPassword, email, oldPassword), connection);
                    connection.Open();
                    int rowCount = command.ExecuteNonQuery();
                    connection.Close();
                    return rowCount > 0;
                }
#endif
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                return false;
            }
        }

        #endregion

        #region Private Members

        private MembershipCreateStatus CreateOneUser(RegisterModel rgData)
        {
            const string sqlInsertTemplate =
                "INSERT INTO users (" +
                    "[user_email], [user_password], [user_firstname], [user_lastname], [user_company], [user_phone_1], " +
                    "[user_phone_2], [user_phone_3], [user_role], [email_them]) VALUES (" +
                    "'{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', 1, {8})";
            string sqlInsert = String.Format(sqlInsertTemplate, rgData.Email, rgData.Password,
                rgData.FirstName ?? String.Empty, rgData.LastName ?? String.Empty, rgData.CompanyName ?? String.Empty,
                rgData.Phone_1 ?? String.Empty, rgData.Phone_2 ?? String.Empty, rgData.Phone_3 ?? String.Empty,
                rgData.EmailThem ? 1 : 0);
            try
            {
#if MS_ACCESS
                using (var connection = new OleDbConnection(_connectionString))
                {
                    var command = new OleDbCommand(sqlInsert, connection);
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                    return MembershipCreateStatus.Success;
                }
#else
                using (var connection = new MySqlConnection(_connectionString))
                {
                    var command = new MySqlCommand(sqlInsert, connection);
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();
                    return MembershipCreateStatus.Success;
                }
#endif            
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                return MembershipCreateStatus.UserRejected;
            }
        }

        private MembershipCreateStatus ValidateData(string email)
        {
            const string sqlSelect = "SELECT COUNT(*) FROM users WHERE (user_email = '{0}')";

            try
            {
#if MS_ACCESS
                using (var connection = new OleDbConnection(_connectionString))
                {
                    var command = new OleDbCommand(String.Format(sqlSelect, email), connection);
                    connection.Open();
                    Int32 count = Convert.ToInt32(command.ExecuteScalar());
                    connection.Close();
                    return count < 1 ? MembershipCreateStatus.Success : MembershipCreateStatus.DuplicateEmail;
                }
#else
                using (var connection = new MySqlConnection(_connectionString))
                {
                    var command = new MySqlCommand(String.Format(sqlSelect, email), connection);
                    connection.Open();
                    Int32 count = Convert.ToInt32(command.ExecuteScalar());
                    connection.Close();
                    return count < 1 ? MembershipCreateStatus.Success : MembershipCreateStatus.DuplicateEmail;
                }
#endif
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                return MembershipCreateStatus.UserRejected;
            }
        }

        private StonePonyIdentity ReadUser(string email)
        {
            const string sqlSelect = "SELECT users.* FROM users WHERE (user_email = '{0}')";

            StonePonyIdentity identity = null;
#if MS_ACCESS
            using (var connection = new OleDbConnection(_connectionString))
            {
                var command = new OleDbCommand(String.Format(sqlSelect, email), connection);
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();

                while (identity == null && reader.Read())
                    identity = new StonePonyIdentity(reader);
                
                reader.Close();
            }
#else
            using (var connection = new MySqlConnection(_connectionString))
            {
                var command = new MySqlCommand(String.Format(sqlSelect, email), connection);
                connection.Open();
                IDataReader reader = command.ExecuteReader();

                while (identity == null && reader.Read())
                    identity = new StonePonyIdentity(reader);

                reader.Close();
            }
#endif
            return identity ?? new StonePonyIdentity();
        }
        #endregion
    }
}